﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.DBM;
using KPIS.GERP.GBMS.DAL;
using KPIS.GERP.GBMS.MODEL;
using KPIS.GERP.GBMS.DALFactory;
using KPIS.GERP.GBMS.IBLL;
using System.Globalization;

namespace KPIS.GERP.GBMS.BLL
{
    public sealed class ApprovalHeader_BLL : IProjectHeader
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public DataTable GetDataProjectHeaderByYear(int Year)  
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Year", Year);
                string sql = "select bgm_app_header_seq as Sequence"
                                + " , (select coalesce(sum( total_amount ), 0 ) from bgm_approval_control a where a.bgm_app_header_seq = ah.bgm_app_header_seq) as total_amount"
                                + " , (select coalesce(sum( approve_amount ), 0 ) from bgm_approval_control a where a.bgm_app_header_seq = ah.bgm_app_header_seq) as approve_amount"
                                + ", ah.bgm_app_header_date as bgm_prj_header_date "
                                + ", ah.bgm_app_header_name as bgm_prj_header_name "
                                + ", ah.bgm_app_header_descr as bgm_prj_header_descr ,ah.* "
                                + ", case when ah.record_status = 'A' then 'อนุมัติ' else 'กำลังดำเนินการ' end as status"
                           + " from bgm_approval_header ah"
                           + " where bgm_year = @Year"
                                + " and record_status not like @RecordStatus";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectHeaderBySequence(string seq)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Sequence", seq);
                string sql = "select bgm_app_header_seq as Sequence"
                           + ", case when from_bgm_app_header_seq is not null"
                           + " then  (select coalesce(sum( total_amount ), 0 ) from bgm_approval a where a.bgm_year = ah.bgm_year and a.bgm_app_header_seq = ah.from_bgm_app_header_seq)"
                           + " else (select coalesce(sum( total_amount ), 0 ) from bgm_request s inner join bgm_request_expense se on s.bgm_req_seq = se.bgm_req_seq where s.bgm_year = "
                           + " ah.bgm_year and s.bgm_req_seq in  (select a.bgm_req_seq from bgm_approval a where a.bgm_app_header_seq = ah.bgm_app_header_seq )) end as total_amount"
                           + " , (select coalesce(sum( a.approve_amount ), 0 ) from bgm_approval a where a.bgm_app_header_seq = ah.bgm_app_header_seq) as approve_amount"
                           + ", ah.bgm_app_header_date as bgm_prj_header_date "
                           + ", ah.bgm_app_header_name as bgm_prj_header_name "
                           + ", ah.bgm_app_header_descr as bgm_prj_header_descr ,ah.* "
                           + " from bgm_approval_header ah"
                           + " where bgm_app_header_seq = @Sequence"
                           + " and record_status not like @RecordStatus";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        public DataTable GetDataProjectBySequence(string seq)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@Sequence", seq);
                string sql = "select *, bgm_app_seq as bgm_prj_seq "
                           + "from bgm_approval "
                           + "where bgm_app_header_seq = @Sequence";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        public int InsertProjectHeader(string bgm_app_header_name, string bgm_app_header_descr, string bgm_app_header_date, string bgm_app_header_result, string bgm_year, string remark, Nullable<int> from_bgm_app_header_seq, string created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(10);
                dbManager.AddParameters(0, "@bgm_app_header_name", bgm_app_header_name);
                dbManager.AddParameters(1, "@bgm_app_header_descr", bgm_app_header_descr);
                dbManager.AddParameters(2, "@bgm_app_header_date", bgm_app_header_date);
                dbManager.AddParameters(3, "@bgm_app_header_result", bgm_app_header_result);
                dbManager.AddParameters(4, "@bgm_year", bgm_year);
                dbManager.AddParameters(5, "@remark", remark);
                dbManager.AddParameters(6, "@from_bgm_app_header_seq", from_bgm_app_header_seq);
                dbManager.AddParameters(7, "@record_status", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(8, "@created_by", created_by);
                dbManager.AddParameters(9, "@created_when", DateTime.Now);
                string strsql = "Insert into bgm_approval_header ("
                                            + "bgm_app_header_name,"
                                            + "bgm_app_header_descr,"
                                            + "bgm_app_header_date,"
                                            + "bgm_app_header_result,"
                                            + "bgm_year,"
                                            + "remark,"
                                            + "from_bgm_app_header_seq,"
                                            + "record_status,"
                                            + "created_by,"
                                            + "created_when)"
                                            + " values("
                                            + "@bgm_app_header_name,"
                                            + "@bgm_app_header_descr,"
                                            + "@bgm_app_header_date,"
                                            + "@bgm_app_header_result,"
                                            + "@bgm_year,"
                                            + "@remark,"
                                            + "@from_bgm_app_header_seq,"
                                            + "@record_status,"
                                            + "@created_by,"
                                            + "@created_when)";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);

                string strsqlLast_insert = "select distinct scope_identity() FROM bgm_approval_header";
                DataSet ds = new DataSet();
                ds = dbManager.ExecuteDataSet(CommandType.Text, strsqlLast_insert);
                dbManager.CommitTransaction();
                return Convert.ToInt32(ds.Tables[0].Rows[0][0]);
            }   
            catch
            {
                return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateApprovalStrategy(string bgm_app_strat_seq, decimal approve_amount, string UpdateBy)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@sequence", bgm_app_strat_seq);
                dbManager.AddParameters(1, "@approve_amount", approve_amount);
                dbManager.AddParameters(2, "@updated_by", UpdateBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Now);
                string strsql = "UPDATE bgm_approval_strategy SET approve_amount=@approve_amount, updated_by=@updated_by, updated_when=@updated_when WHERE bgm_app_strat_seq=@sequence";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {

            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void UpdateApprovalSource(string bgm_app_source_seq, decimal approve_amount, string UpdateBy)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@sequence", bgm_app_source_seq);
                dbManager.AddParameters(1, "@approve_amount", approve_amount);
                dbManager.AddParameters(2, "@updated_by", UpdateBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Now);
                string strsql = "UPDATE bgm_approval_source SET approve_amount=@approve_amount, updated_by=@updated_by, updated_when=@updated_when WHERE bgm_app_source_seq=@sequence";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {

            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void InsertApprovalStrategy(string bgm_app_header_seq, string bgm_strat_list_seq, string bgm_source_seq, decimal total_amount, decimal approve_amount, string created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);
                dbManager.AddParameters(0, "@bgm_app_header_seq", bgm_app_header_seq);
                dbManager.AddParameters(1, "@bgm_strat_list_seq", bgm_strat_list_seq);
                dbManager.AddParameters(2, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(3, "@total_amount", total_amount);
                dbManager.AddParameters(4, "@approve_amount", approve_amount);
                dbManager.AddParameters(5, "@created_by", created_by);
                dbManager.AddParameters(6, "@created_when", DateTime.Now);

                string strsql = "Insert into bgm_approval_strategy ("
                            + "bgm_app_header_seq,"
                            + "bgm_strat_list_seq,"
                            + "bgm_source_seq,"
                            + "total_amount,"
                            + "approve_amount,"
                            + "created_by,"
                            + "created_when)"
                            + " values("
                            + "@bgm_app_header_seq,"
                            + "@bgm_strat_list_seq,"
                            + "@bgm_source_seq,"
                            + "@total_amount,"
                            + "@approve_amount,"
                            + "@created_by,"
                            + "@created_when)";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {
                //dbManager.RollBackTransaction();
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void InsertApprovalSource(string bgm_app_seq, string bgm_source_seq, decimal total_amount, decimal approve_amount, string created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@bgm_app_seq", bgm_app_seq);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@total_amount", total_amount);
                dbManager.AddParameters(3, "@approve_amount", approve_amount);
                dbManager.AddParameters(4, "@created_by", created_by);
                dbManager.AddParameters(5, "@created_when", DateTime.Now);

                string strsql = "Insert into bgm_approval_source ("
                            + "bgm_app_seq,"
                            + "bgm_source_seq,"
                            + "total_amount,"
                            + "approve_amount,"
                            + "created_by,"
                            + "created_when)"
                            + " values("
                            + "@bgm_app_seq,"
                            + "@bgm_source_seq,"
                            + "@total_amount,"
                            + "@approve_amount,"
                            + "@created_by,"
                            + "@created_when)";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {
                //dbManager.RollBackTransaction();
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void InsertProject(string bgm_app_header_seq, string bgm_req_seq, string created_by)
        {
          try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@app", bgm_app_header_seq);
                dbManager.AddParameters(1, "@req", bgm_req_seq);
                dbManager.AddParameters(2, "@usr", created_by);
                dbManager.AddParameters(3, "@created_when", DateTime.Now);

                string strsql = "select F_Auto_Create_New_Approval(@app,@req,@usr)";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);


                

                //strsql = "select F_Auto_Create_New_Approval_Control(@app,@usr)";    //------------------  ไว้อีกฟักชั่น  ไม่ต้อง Loop
                //dbManager.ExecuteNonQuery(CommandType.Text, strsql);


 #region

                ////bgm_approval 
                //string strsql = "Insert into bgm_approval ("
                //                + "bgm_app_header_seq,"
                //                + "bgm_req_seq,"
                //                + "bgm_app_code,"
                //                + "bgm_app_name,"
                //                + "bgm_app_descr,"
                //                + "bgm_app_manager,"
                //                + "bgm_app_scope,"
                //                + "bgm_app_date,"
                //                + "bgm_app_reason,"
                //                + "bgm_app_object,"
                //                + "bgm_app_target,"
                //                + "bgm_app_plan,"
                //                + "bgm_app_remark,"
                //                + "bgm_strat_list_seq,"
                //                + "prj_type_seq,"
                //                + "job_site_seq,"
                //                + "bgm_year,"
                //                + "created_by) select "
                //                + "@bgm_app_header_seq,"
                //                + "bgm_req_seq,"
                //                + "bgm_req_code,"
                //                + "bgm_req_name,"
                //                + "bgm_req_descr,"
                //                + "bgm_req_manager,"
                //                + "bgm_req_scope,"
                //                + "bgm_req_date,"
                //                + "bgm_req_reason,"
                //                + "bgm_req_object,"
                //                + "bgm_req_target,"
                //                + "bgm_req_plan,"
                //                + "bgm_req_remark,"
                //                + "bgm_strat_list_seq,"
                //                + "prj_type_seq,"
                //                + "job_site_seq,"
                //                + "bgm_year,"
                //                + "@created_by"
                //                + " from bgm_request"
                //                + " where bgm_req_seq = @bgm_req_seq";
                //dbManager.ExecuteNonQuery(CommandType.Text, strsql);

                ////bgm_approval_expense
                //strsql = "Insert into bgm_approval_expense ("
                //                + "bgm_app_seq,"
                //                + "bgm_req_exp_seq,"
                //                + "descr,"
                //                + "total_amount,"
                //                + "remark,"
                //                + "mtef1,"
                //                + "mtef2,"
                //                + "mtef3,"
                //                + "bgm_source_seq,"
                //                + "bgm_exp_seq,"
                //                + "icm_res_item_seq,"
                //                + "unit_price,"
                //                + "unit_qty,"
                //                + "created_by) select "
                //                + "(select distinct scope_identity() FROM bgm_approval),"
                //                + "bgm_req_exp_seq,"
                //                + "descr,"
                //                + "total_amount,"
                //                + "remark,"
                //                + "mtef1,"
                //                + "mtef2,"
                //                + "mtef3,"
                //                + "bgm_source_seq,"
                //                + "bgm_exp_seq,"
                //                + "icm_res_item_seq,"
                //                + "unit_price,"
                //                + "unit_qty,"
                //                + "@created_by"
                //                + " from bgm_request_expense"
                //                + " where bgm_req_seq = @bgm_req_seq";
                //dbManager.ExecuteNonQuery(CommandType.Text, strsql);

                ////bgm_approval_expense_plan   
                //strsql = "Insert into bgm_approval_expense_plan ("
                //                + "bgm_app_exp_seq,"
                //                + "amount_month1,"
                //                + "amount_month2,"
                //                + "amount_month3,"
                //                + "amount_month4,"
                //                + "amount_month5,"
                //                + "amount_month6,"
                //                + "amount_month7,"
                //                + "amount_month8,"
                //                + "amount_month9,"
                //                + "amount_month10,"
                //                + "amount_month11,"
                //                + "amount_month12,"
                //                + "created_by) select "
                //                + "(select distinct scope_identity() FROM bgm_approval_expense),"
                //                + "amount_month1,"
                //                + "amount_month2,"
                //                + "amount_month3,"
                //                + "amount_month4,"
                //                + "amount_month5,"
                //                + "amount_month6,"
                //                + "amount_month7,"
                //                + "amount_month8,"
                //                + "amount_month9,"
                //                + "amount_month10,"
                //                + "amount_month11,"
                //                + "amount_month12,"
                //                + "@created_by"
                //                + " from bgm_request r"
                //                    + " inner join bgm_request_expense e on r.bgm_req_seq = e.bgm_req_seq" 
                //                    + " inner join bgm_request_expense_plan p on e.bgm_req_exp_seq = p.bgm_req_exp_seq"
                //                + " where r.bgm_req_seq = @bgm_req_seq";
                //dbManager.ExecuteNonQuery(CommandType.Text, strsql);

                //DataTable dt = dbManager.ExecuteDataSet(CommandType.Text, "select distinct scope_identity() FROM bgm_approval").Tables[0];

                ////bgm_approval_activity_plan   
                //strsql = "Insert into bgm_approval_activity_plan ("
                //                + "bgm_app_seq,"
                //                + "descr,"
                //                + "act_month1,"
                //                + "act_month2,"
                //                + "act_month3,"
                //                + "act_month4,"
                //                + "act_month5,"
                //                + "act_month6,"
                //                + "act_month7,"
                //                + "act_month8,"
                //                + "act_month9,"
                //                + "act_month10,"
                //                + "act_month11,"
                //                + "act_month12,"
                //                + "created_by) select "
                //                + "(select distinct scope_identity() FROM bgm_approval),"
                //                + "descr,"
                //                + "act_month1,"
                //                + "act_month2,"
                //                + "act_month3,"
                //                + "act_month4,"
                //                + "act_month5,"
                //                + "act_month6,"
                //                + "act_month7,"
                //                + "act_month8,"
                //                + "act_month9,"
                //                + "act_month10,"
                //                + "act_month11,"
                //                + "act_month12,"
                //                + "@created_by"
                //                + " from bgm_request_activitiy_plan"
                //                + " where bgm_req_seq = @bgm_req_seq";
                //dbManager.ExecuteNonQuery(CommandType.Text, strsql);


                ////bgm_approval_activity_plan_target   
                //strsql = "Insert into bgm_approval_activity_plan_target ("
                //                + "bgm_app_act_plan_seq,"
                //                + "target,"
                //                + "start_month,"
                //                + "end_month,"
                //                + "created_by) select "
                //                + "(select distinct scope_identity() FROM bgm_approval_activity_plan),"
                //                + "target,"
                //                + "start_month,"
                //                + "end_month,"
                //                + "@created_by"
                //                + " from bgm_request_activitiy_plan p"
                //                + " inner join bgm_request_activity_plan_target pt on p.bgm_req_act_plan_seq = pt.bgm_req_act_plan_seq"
                //                + " where p.bgm_req_seq = @bgm_req_seq";
                //dbManager.ExecuteNonQuery(CommandType.Text, strsql);

                ////bgm_approval_option   
                //strsql = "Insert into bgm_approval_option ("
                //                + "bgm_app_seq,"
                //                + "descr,"
                //                + "weight,"
                //                + "bgm_option_seq,"
                //                + "created_by) select "
                //                + "(select distinct scope_identity() FROM bgm_approval),"
                //                + "descr,"
                //                + "weight,"
                //                + "bgm_option_seq,"
                //                + "@created_by"
                //                + " from bgm_request_option"
                //                + " where bgm_req_seq = @bgm_req_seq";
                //dbManager.ExecuteNonQuery(CommandType.Text, strsql);

                ////bgm_approval_expense_field   
                //strsql = "Insert into bgm_approval_expense_field ("
                //                + "bgm_app_exp_seq,"
                //                + "exp_rate_ctrl_seq,"
                //                + "descr) select "
                //                + "(select distinct scope_identity() FROM bgm_approval_expense),"
                //                + "exp_rate_ctrl_seq,"
                //                + "f.descr"
                //                +" from bgm_request r"
                //                    + " inner join bgm_request_expense e on r.bgm_req_seq = e.bgm_req_seq"
                //                    + " inner join bgm_request_expense_field f on e.bgm_req_exp_seq = f.bgm_req_exp_seq"
                //                + " where r.bgm_req_seq = @bgm_req_seq";
                //dbManager.ExecuteNonQuery(CommandType.Text, strsql);


                ////bgm_approval_kpi   
                //strsql = "Insert into bgm_approval_kpi ("
                //                + "bgm_app_kpi_name,"
                //                + "bgm_app_kpi_descr,"
                //                + "unit,"
                //                + "bgm_app_kpi_weight,"
                //                + "bgm_app_seq,"
                //                + "bgm_kpi_type_seq,"
                //                + "score_1,"
                //                + "score_2,"
                //                + "score_3,"
                //                + "score_4,"
                //                + "score_5,"
                //                + "target_type,"
                //                + "target_q1,"
                //                + "target_q2,"
                //                + "target_q3,"
                //                + "target_q4,"
                //                + "mtef_1,"
                //                + "mtef_2,"
                //                + "mtef_3,"
                //                + "created_by) select "
                //                + "bgm_req_kpi_name,"
                //                + "bgm_req_kpi_descr,"
                //                + "unit,"
                //                + "bgm_req_kpi_weight,"
                //                + "(select distinct scope_identity() FROM bgm_approval),"
                //                + "bgm_kpi_type_seq,"
                //                + "score_1,"
                //                + "score_2,"
                //                + "score_3,"
                //                + "score_4,"
                //                + "score_5,"
                //                + "target_type,"
                //                + "target_q1,"
                //                + "target_q2,"
                //                + "target_q3,"
                //                + "target_q4,"
                //                + "mtef_1,"
                //                + "mtef_2,"
                //                + "mtef_3,"
                //                + "@created_by"
                //                + " from bgm_request_kpi"
                //                + " where bgm_req_seq = @bgm_req_seq";
                //dbManager.ExecuteNonQuery(CommandType.Text, strsql);


                ////bgm_approval_customs_field   
                //strsql = "Insert into bgm_approval_customs_field ("
                //                + "bgm_app_seq,"
                //                + "prj_ctrl_seq,"
                //                + "descr) select "
                //                + "(select distinct scope_identity() FROM bgm_approval),"
                //                + "prj_ctrl_seq,"
                //                + "descr"
                //                + " from bgm_request_customs_field"
                //                + " where bgm_req_seq = @bgm_req_seq";
                //dbManager.ExecuteNonQuery(CommandType.Text, strsql);

 #endregion

                dbManager.CommitTransaction();
            }   
            catch
            {
                dbManager.RollBackTransaction();
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void InsertProjectControl(string bgm_app_header_seq, string created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@app", bgm_app_header_seq);
                dbManager.AddParameters(1, "@usr", created_by);
                dbManager.AddParameters(2, "@created_when", DateTime.Now);

                string strsql = "select F_Auto_Create_New_Approval_Control(@app,@usr)";    //ไม่ต้อง Loop
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
            }
            finally
            {
                dbManager.Dispose();
            }
        }



        public void UpdateProjectHeader(string Sequence, string bgm_year, string bgm_app_header_date, string bgm_app_header_name, string bgm_app_header_descr, string remark, string UpdateBy)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@bgm_year", bgm_year);
                dbManager.AddParameters(2, "@bgm_app_header_date", bgm_app_header_date);
                dbManager.AddParameters(3, "@bgm_app_header_name", bgm_app_header_name);
                dbManager.AddParameters(4, "@bgm_app_header_descr", bgm_app_header_descr);
                dbManager.AddParameters(5, "@remark", remark);
                dbManager.AddParameters(6, "@updated_by", UpdateBy);
                dbManager.AddParameters(7, "@updated_when", DateTime.Now);
                string strsql = "UPDATE bgm_approval_header SET bgm_year=@bgm_year, bgm_app_header_date=@bgm_app_header_date, bgm_app_header_name=@bgm_app_header_name, bgm_app_header_descr=@bgm_app_header_descr, remark=@remark, updated_by=@updated_by, updated_when=@updated_when WHERE bgm_app_header_seq=@sequence";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {

            }
            finally
            {
                dbManager.Dispose();
            }
        }



        public void DeleteProjectHeader(int Seq)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@Seq", Seq);
                string strsql = "DELETE FROM bgm_approval_header WHERE bgm_app_header_seq = @Seq";
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
                dbManager.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DeleteProject(int Seq)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@Seq", Seq);
                string strsql = "DELETE FROM bgm_approval WHERE bgm_app_seq = @Seq;";
                strsql += "DELETE FROM bgm_approval_option WHERE bgm_app_seq = @Seq;";
                strsql += "DELETE FROM bgm_approval_kpi WHERE bgm_app_seq = @Seq;";
                strsql += "DELETE FROM bgm_approval_customs_field WHERE bgm_app_seq = @Seq;";

                strsql += "DELETE FROM bgm_approval_activity_plan_target WHERE bgm_app_act_plan_seq in (select bgm_app_act_plan_seq from bgm_approval_activity_plan where bgm_app_seq = @Seq);";
                strsql += "DELETE FROM bgm_approval_activity_plan WHERE bgm_app_seq = @Seq;";

                strsql += "DELETE FROM bgm_approval_expense_field WHERE bgm_app_exp_seq in (select bgm_app_exp_seq from bgm_approval_expense where bgm_app_seq = @Seq);";
                strsql += "DELETE FROM bgm_approval_expense_plan WHERE bgm_app_exp_seq in (select bgm_app_exp_seq from bgm_approval_expense where bgm_app_seq = @Seq);";
                strsql += "DELETE FROM bgm_approval_expense WHERE bgm_app_seq = @Seq;";

                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
                dbManager.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable ListRequest(string bgm_year, string bgm_strat_list_seq, string job_site_seq, string bgm_app_header_seq)
        {
            try
            {
                string sql = " select r.bgm_req_seq as VALUES "
                           + ", concat( ' ['"
                           + " 'เข้าที่ประชุม ' , (select count(a.bgm_app_seq) from bgm_approval a where a.bgm_req_seq = r.bgm_req_seq), ' ครั้ง]-',r.bgm_req_name ) as Text"
                           + " from bgm_request r"
                           + " where r.bgm_year = " + bgm_year;
                if (bgm_strat_list_seq.Length > 0)
                {
                    sql += " and r.bgm_strat_list_seq = " + bgm_strat_list_seq;
                }
                if (job_site_seq.Length > 0)
                {
                    sql += " and r.job_site_seq = " + job_site_seq;
                }
                if (bgm_app_header_seq.Length > 0)
                {
                    sql += " and r.bgm_req_seq not in (select a2.bgm_req_seq from bgm_approval a2 where a2.bgm_year = " + bgm_year + " and a2.bgm_app_header_seq = " + bgm_app_header_seq + " )";
                }
                sql += " and r.record_status = 'A'";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable ListApproval(string bgm_app_header_seq)
        {
            try
            {
                string sql = " select bgm_app_seq as Value, bgm_app_name as Text, bgm_app_seq, bgm_req_seq"
                           + " from bgm_approval" 
                           + " where record_status='W' and bgm_app_header_seq = " + bgm_app_header_seq;
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void ApproveTransaction(string HeaderID, string UserID)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@HeaderID", HeaderID);
                string strsql = "UPDATE bgm_approval_header SET record_status = 'A' WHERE bgm_app_header_seq = @HeaderID";
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }


        //public DataTable GetDataProjectSourceBySequence(string seq)
        //{
        //    try
        //    {
        //        dbManager.CreateParameters(2);
        //        dbManager.AddParameters(0, "@seq", seq);
        //        dbManager.AddParameters(1, "@RecordStatus", SystemInfo._DeleteRecordStatus);

        //        string sql = "select r.bgm_strat_list_seq, s.bgm_source_seq"
        //                    +", s.bgm_source_name"
        //                    +", sum(re.total_amount) as total_amount "
        //                    +" from  bgm_mas_source s "
        //                    +" left join bgm_request_expense re on re.bgm_source_seq = s.bgm_source_seq and re.bgm_req_seq = @seq"
        //                    +" left join bgm_request  r on re.bgm_req_seq = r.bgm_req_seq"
        //                    +" where s.record_status not like @RecordStatus"
        //                    +" and s.bgm_source_seq not in (select upper_bgm_source_seq from bgm_mas_source where record_status not like @RecordStatus and upper_bgm_source_seq is not null)"
        //                    +" group by s.bgm_source_seq, s.bgm_source_name"
        //                    +" order by s.bgm_source_code";

        //        DataTable dt = new DataTable();
        //        dbManager.Open();
        //        dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
        //        return dt;
        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }
        //}

        public DataTable GetDataProjectSourceBySequence(string bgm_prj_header_seq, string bgm_prj_seq)
        {
            try
            {
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@bgm_prj_header_seq", bgm_prj_header_seq);
                dbManager.AddParameters(1, "@bgm_prj_seq", bgm_prj_seq);
                dbManager.AddParameters(2, "@RecordStatus", SystemInfo._DeleteRecordStatus);

                string sql = "select a.bgm_app_seq, a.bgm_strat_list_seq, s.bgm_source_seq"
                            + ", s.bgm_source_name"
                            + ", sum(ae.total_amount) as total_amount "
                            + " from  bgm_mas_source s "
                            + " left join bgm_approval_expense ae on ae.bgm_source_seq = s.bgm_source_seq and ae.bgm_app_seq = (select bgm_app_seq from bgm_approval where bgm_req_seq = @bgm_prj_seq and bgm_app_header_seq = @bgm_prj_header_seq)"

                            + " left join bgm_approval a on ae.bgm_app_seq = a.bgm_app_seq"
                            + " where s.record_status not like @RecordStatus"

                            + " and s.bgm_source_seq not in (select upper_bgm_source_seq from bgm_mas_source where record_status not like @RecordStatus and upper_bgm_source_seq is not null)"
                            + " group by s.bgm_source_seq, s.bgm_source_name"
                            + " order by s.bgm_source_code";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
